﻿/*=================================================================
* 版权信息：Copyright (C) 2005-2011 捷点科技 All Rights Reserved.
* 文件名称：CNVP_Products.cs
* 创建标识：曾乃强 15158756652 2011-03-17 11:03:01
* 修改标识：
* 功能描述：
===================================================================*/
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using CNVP.Framework.Helper;
using CNVP.Framework;
using CNVP.Config;

namespace CNVP.JShop.DAL
{
    /// <summary>
    /// 对*CNVP_Products*操作数据库(针对SQL Server)的实现
    /// </summary>
    public class CNVP_Products
    {
       private string Prefix = DBConfig.Prefix;
        #region DAL
        public int Insert(Model.CNVP_Products obj)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("insert into  " + Prefix + "Products ([ProductName],[ProductClassID],[ProductTypeID],[BrandID],[Intro],[Detaile],[MarketPrice],[TradePrice],[ProductCode],[KeyWords],[ProductUnit],[ProductWeight],[CostPrice],[ProductNo],[StockTotalNum],[Allocation],[ProductTags],[ProductState],[AddTime],[StandardIDs], [StandardValues],[ImgProLoad],[IsDelete],[SeoInfoID],[HotLink],[AllowReview],[DeliveryFreightID])");
            sql.Append("values(@ProductName,@ProductClassID,@ProductTypeID,@BrandID,@Intro,@Detaile,@MarketPrice,@TradePrice,@ProductCode,@KeyWords,@ProductUnit,@ProductWeight,@CostPrice,@ProductNo,@StockTotalNum,@Allocation,@ProductTags,@ProductState,@AddTime, @StandardIDs, @StandardValues,@ImgProLoad,@IsDelete,@SeoInfoID,@HotLink,@AllowReview,@DeliveryFreightID)");
            sql.Append(";SELECT @@IDENTITY");
            SqlParameter[] pra = {
                            new SqlParameter("@ProductName",obj.ProductName),
							new SqlParameter("@ProductClassID",obj.ProductClassID),
							new SqlParameter("@ProductTypeID",obj.ProductTypeID),
							new SqlParameter("@BrandID",obj.BrandID),
							new SqlParameter("@Intro",obj.Intro),
							new SqlParameter("@Detaile",obj.Detaile),
							new SqlParameter("@MarketPrice",obj.MarketPrice),
							new SqlParameter("@TradePrice",obj.TradePrice),
							new SqlParameter("@ProductCode",obj.ProductCode),
							new SqlParameter("@KeyWords",obj.KeyWords),
							new SqlParameter("@ProductUnit",obj.ProductUnit),
							new SqlParameter("@ProductWeight",obj.ProductWeight),
							new SqlParameter("@CostPrice",obj.CostPrice),
							new SqlParameter("@ProductNo",obj.ProductNo),
							new SqlParameter("@StockTotalNum",obj.StockTotalNum),
							new SqlParameter("@Allocation",obj.Allocation),
							new SqlParameter("@ProductTags",obj.ProductTags),
							new SqlParameter("@ProductState",obj.ProductState),
							new SqlParameter("@AddTime",obj.AddTime),
                            new SqlParameter("@StandardIDs",obj.StandardIDs),
							new SqlParameter("@StandardValues",obj.StandardValues),
                            new SqlParameter("@ImgProLoad",obj.ImgProLoad),
                            new SqlParameter("@IsDelete",obj.IsDelete),
                            new SqlParameter("@SeoInfoID",obj.SeoInfoID),
                            new SqlParameter("@HotLink",obj.HotLink),
                            new SqlParameter("@AllowReview",obj.AllowReview),
                            new SqlParameter("@DeliveryFreightID",obj.DeliveryFreightID)
                                 };
            return Convert.ToInt32(DbHelper.ExecuteScalar(sql.ToString(), pra));
        }

        public int Update(Model.CNVP_Products obj)
        {
             StringBuilder sql = new StringBuilder();
             sql.Append("update " + Prefix + "Products set [ProductName] = @ProductName,[ProductClassID] = @ProductClassID,[ProductTypeID] = @ProductTypeID,[BrandID] = @BrandID,[Intro] = @Intro,[Detaile] = @Detaile,[MarketPrice] = @MarketPrice,[TradePrice] = @TradePrice,[ProductCode] = @ProductCode,[UpTime] = @UpTime,[DownTime] = @DownTime,[EditTime] = @EditTime,[KeyWords] = @KeyWords,[ProductUnit] = @ProductUnit,[ProductWeight] = @ProductWeight,[CostPrice] = @CostPrice,[ProductNo] = @ProductNo,[StockTotalNum] = @StockTotalNum,[Allocation] = @Allocation,[ProductTags] = @ProductTags,[ProductState] = @ProductState,[StandardIDs]=@StandardIDs,[StandardValues]=@StandardValues,[ImgProLoad]=@ImgProLoad,[SeoInfoID]=@SeoInfoID,[HotLink]=@HotLink,[AllowReview]=@AllowReview,[DeliveryFreightID]=@DeliveryFreightID where [ID] = @ID");

            SqlParameter[] pra = { 
                            new SqlParameter("@ID",obj.ID),
							new SqlParameter("@ProductName",obj.ProductName),
							new SqlParameter("@ProductClassID",obj.ProductClassID),
							new SqlParameter("@ProductTypeID",obj.ProductTypeID),
							new SqlParameter("@BrandID",obj.BrandID),
							new SqlParameter("@Intro",obj.Intro),
							new SqlParameter("@Detaile",obj.Detaile),
							new SqlParameter("@MarketPrice",obj.MarketPrice),
							new SqlParameter("@TradePrice",obj.TradePrice),
							new SqlParameter("@ProductCode",obj.ProductCode),
							new SqlParameter("@UpTime",obj.UpTime),
							new SqlParameter("@DownTime",obj.DownTime),
							new SqlParameter("@EditTime",obj.EditTime),
							new SqlParameter("@KeyWords",obj.KeyWords),
							new SqlParameter("@ProductUnit",obj.ProductUnit),
							new SqlParameter("@ProductWeight",obj.ProductWeight),
							new SqlParameter("@CostPrice",obj.CostPrice),
							new SqlParameter("@ProductNo",obj.ProductNo),
							new SqlParameter("@StockTotalNum",obj.StockTotalNum),
							new SqlParameter("@Allocation",obj.Allocation),
							new SqlParameter("@ProductTags",obj.ProductTags),
							new SqlParameter("@ProductState",obj.ProductState),
                            new SqlParameter("@StandardIDs",obj.StandardIDs),
							new SqlParameter("@StandardValues",obj.StandardValues),
                            new SqlParameter("@ImgProLoad",obj.ImgProLoad),
                            new SqlParameter("@SeoInfoID",obj.SeoInfoID),
                            new SqlParameter("@HotLink",obj.HotLink),
                            new SqlParameter("@AllowReview",obj.AllowReview),
                            new SqlParameter("@DeliveryFreightID",obj.DeliveryFreightID)
                                };
             return DbHelper.ExecuteNonQuery(sql.ToString(),pra);
        }

        public int Delete(Model.CNVP_Products obj)
        {
           StringBuilder sql = new StringBuilder();
           sql.Append("delete from " + Prefix + "Products where [ID] = @ID");
           SqlParameter[] pra = {
                                new SqlParameter("@ID",obj.ID)
						
                                };
           return DbHelper.ExecuteNonQuery(sql.ToString(),pra);
        }

        public List<Model.CNVP_Products> LoadAll(string sqlWhere)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select [ID],[ProductName],[ProductClassID],[ProductTypeID],[BrandID],[Intro],[Detaile],[MarketPrice],[TradePrice],[ProductCode],[UpTime],[DownTime],[EditTime],[KeyWords],[ProductUnit],[ProductWeight],[CostPrice],[ProductNo],[StockTotalNum],[Allocation],[ProductTags],[IsDelete],[ProductState],[SeoInfoID],[AddTime],[StandardIDs],[StandardValues],[ImgProLoad],[AllowReview],[DeliveryFreightID] from " + Prefix + "Products");
            if (sqlWhere.Trim() != "")
            {
                sql.Append(" where " + sqlWhere);
            }
            DataTable DT = DbHelper.ExecuteTable(sql.ToString());
            List<Model.CNVP_Products> list=new List<Model.CNVP_Products>();
            if (DT != null && DT.Rows.Count > 0)
            {
                foreach (DataRow dr in DT.Rows)
                {
                    Model.CNVP_Products cnvp_products = new Model.CNVP_Products();
                    cnvp_products.ID = dr["ID"] is DBNull ? 0 : (int)dr["ID"];
                    cnvp_products.ProductName = dr["ProductName"] is DBNull ? String.Empty : (string)dr["ProductName"];
                    cnvp_products.ProductClassID = dr["ProductClassID"] is DBNull ? 0 : (int)dr["ProductClassID"];
                    cnvp_products.ProductTypeID = dr["ProductTypeID"] is DBNull ? 0 : (int)dr["ProductTypeID"];
                    cnvp_products.BrandID = dr["BrandID"] is DBNull ? 0 : (int)dr["BrandID"];
                    cnvp_products.Intro = dr["Intro"] is DBNull ? String.Empty : (string)dr["Intro"];
                    cnvp_products.Detaile = dr["Detaile"] is DBNull ? String.Empty : (string)dr["Detaile"];
                    cnvp_products.MarketPrice = dr["MarketPrice"] is DBNull ? (decimal)0.00 : (decimal)dr["MarketPrice"];
                    cnvp_products.TradePrice = dr["TradePrice"] is DBNull ? (decimal)0.00 : (decimal)dr["TradePrice"];
                    cnvp_products.ProductCode = dr["ProductCode"] is DBNull ? String.Empty : (string)dr["ProductCode"];
                    cnvp_products.UpTime = dr["UpTime"] is DBNull ? DateTime.Now : (DateTime)dr["UpTime"];
                    cnvp_products.DownTime = dr["DownTime"] is DBNull ? DateTime.Now : (DateTime)dr["DownTime"];
                    cnvp_products.EditTime = dr["EditTime"] is DBNull ? DateTime.Now : (DateTime)dr["EditTime"];
                    cnvp_products.KeyWords = dr["KeyWords"] is DBNull ? String.Empty : (string)dr["KeyWords"];
                    cnvp_products.ProductUnit = dr["ProductUnit"] is DBNull ? String.Empty : (string)dr["ProductUnit"];
                    cnvp_products.ProductWeight = dr["ProductWeight"] is DBNull ? (decimal)0.00 : (decimal)dr["ProductWeight"];
                    cnvp_products.CostPrice = dr["CostPrice"] is DBNull ? (decimal)0.00 : (decimal)dr["CostPrice"];
                    cnvp_products.ProductNo = dr["ProductNo"] is DBNull ? String.Empty : (string)dr["ProductNo"];
                    cnvp_products.StockTotalNum = dr["StockTotalNum"] is DBNull ? 0 : (int)dr["StockTotalNum"];
                    cnvp_products.Allocation = dr["Allocation"] is DBNull ? String.Empty : (string)dr["Allocation"];
                    cnvp_products.ProductTags = dr["ProductTags"] is DBNull ? String.Empty : (string)dr["ProductTags"];
                    cnvp_products.IsDelete = dr["IsDelete"] is DBNull ? (byte)0 : (byte)dr["IsDelete"];
                    cnvp_products.AllowReview = dr["AllowReview"] is DBNull ? (byte)0 : (byte)dr["AllowReview"];
                    cnvp_products.ProductState = dr["ProductState"] is DBNull ? (byte)0 : (byte)dr["ProductState"];
                    cnvp_products.SeoInfoID = dr["SeoInfoID"] is DBNull ? (int)0 : (int)dr["SeoInfoID"];
                    cnvp_products.AddTime = dr["AddTime"] is DBNull ? DateTime.Now : (DateTime)dr["AddTime"];
                    cnvp_products.StandardIDs = dr["StandardIDs"] is DBNull ? String.Empty : (string)dr["StandardIDs"];
                    cnvp_products.StandardValues = dr["StandardValues"] is DBNull ? String.Empty : (string)dr["StandardValues"];
                    cnvp_products.ImgProLoad = dr["ImgProLoad"] is DBNull ? String.Empty : (string)dr["ImgProLoad"];
                    cnvp_products.DeliveryFreightID = dr["DeliveryFreightID"] is DBNull ? (int)0 : (int)dr["DeliveryFreightID"];
                    list.Add(cnvp_products);
                }
            }
            return list;

        }

        public List<Model.CNVP_Products> LoadDeliveryFreightIDInProductIDSBroupBy(string ProductIDS)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select [DeliveryFreightID] from " + Prefix + "Products where ID in (" + ProductIDS + ") group by [DeliveryFreightID] ");
            DataTable DT = DbHelper.ExecuteTable(sql.ToString());
            List<Model.CNVP_Products> list = new List<Model.CNVP_Products>();
            if (DT != null && DT.Rows.Count > 0)
            {
                foreach (DataRow dr in DT.Rows)
                {
                    Model.CNVP_Products cnvp_products = new Model.CNVP_Products();
                    cnvp_products.DeliveryFreightID = dr["DeliveryFreightID"] is DBNull ? (int)0 : (int)dr["DeliveryFreightID"];
                    list.Add(cnvp_products);
                }
            }
            return list;

        }

        public Model.CNVP_Products LoadById(int ID)
        {
            
            StringBuilder sql = new StringBuilder();
            sql.Append("select [ID],[ProductName],[ProductClassID],[ProductTypeID],[BrandID],[Intro],[Detaile],[MarketPrice],[TradePrice],[ProductCode],[UpTime],[DownTime],[EditTime],[KeyWords],[ProductUnit],[ProductWeight],[CostPrice],[ProductNo],[StockTotalNum],[Allocation],[ProductTags],[IsDelete],[ProductState],[SeoInfoID],[AddTime],[StandardIDs],[StandardValues],[ImgProLoad],[HotLink],[AllowReview],[DeliveryFreightID] from " + Prefix + "Products");
            sql.Append(" where [ID] = @ID");
            SqlParameter[] pra = {
                                 new SqlParameter("@ID",ID)
						
                                    };
            DataTable DT = DbHelper.ExecuteTable(sql.ToString(),pra);
            Model.CNVP_Products cnvp_products = new Model.CNVP_Products();
            if (DT != null && DT.Rows.Count > 0)
            {
                DataRow dr = DT.Rows[0];
                cnvp_products.ID = dr["ID"] is DBNull ? 0 : (int)dr["ID"];
                cnvp_products.ProductName = dr["ProductName"] is DBNull ? String.Empty : (string)dr["ProductName"];
                cnvp_products.ProductClassID = dr["ProductClassID"] is DBNull ? 0 : (int)dr["ProductClassID"];
                cnvp_products.ProductTypeID = dr["ProductTypeID"] is DBNull ? 0 : (int)dr["ProductTypeID"];
                cnvp_products.BrandID = dr["BrandID"] is DBNull ? 0 : (int)dr["BrandID"];
                cnvp_products.Intro = dr["Intro"] is DBNull ? String.Empty : (string)dr["Intro"];
                cnvp_products.Detaile = dr["Detaile"] is DBNull ? String.Empty : (string)dr["Detaile"];
                cnvp_products.MarketPrice = dr["MarketPrice"] is DBNull ? (decimal)0.00 : (decimal)dr["MarketPrice"];
                cnvp_products.TradePrice = dr["TradePrice"] is DBNull ? (decimal)0.00 : (decimal)dr["TradePrice"];
                cnvp_products.ProductCode = dr["ProductCode"] is DBNull ? String.Empty : (string)dr["ProductCode"];
                cnvp_products.UpTime = dr["UpTime"] is DBNull ? DateTime.Now : (DateTime)dr["UpTime"];
                cnvp_products.DownTime = dr["DownTime"] is DBNull ? DateTime.Now : (DateTime)dr["DownTime"];
                cnvp_products.EditTime = dr["EditTime"] is DBNull ? DateTime.Now : (DateTime)dr["EditTime"];
                cnvp_products.KeyWords = dr["KeyWords"] is DBNull ? String.Empty : (string)dr["KeyWords"];
                cnvp_products.ProductUnit = dr["ProductUnit"] is DBNull ? String.Empty : (string)dr["ProductUnit"];
                cnvp_products.ProductWeight = dr["ProductWeight"] is DBNull ? (decimal)0.00 : (decimal)dr["ProductWeight"];
                cnvp_products.CostPrice = dr["CostPrice"] is DBNull ? (decimal)0.00 : (decimal)dr["CostPrice"];
                cnvp_products.ProductNo = dr["ProductNo"] is DBNull ? String.Empty : (string)dr["ProductNo"];
                cnvp_products.StockTotalNum = dr["StockTotalNum"] is DBNull ? 0 : (int)dr["StockTotalNum"];
                cnvp_products.Allocation = dr["Allocation"] is DBNull ? String.Empty : (string)dr["Allocation"];
                cnvp_products.ProductTags = dr["ProductTags"] is DBNull ? String.Empty : (string)dr["ProductTags"];
                cnvp_products.IsDelete = dr["IsDelete"] is DBNull ? (byte)0 : (byte)dr["IsDelete"];
                cnvp_products.AllowReview = dr["AllowReview"] is DBNull ? (byte)0 : (byte)dr["AllowReview"];
                cnvp_products.ProductState = dr["ProductState"] is DBNull ? (byte)0 : (byte)dr["ProductState"];
                cnvp_products.SeoInfoID = dr["SeoInfoID"] is DBNull ? (int)0 : (int)dr["SeoInfoID"];
                cnvp_products.AddTime = dr["AddTime"] is DBNull ? DateTime.Now : (DateTime)dr["AddTime"];
                cnvp_products.StandardIDs = dr["StandardIDs"] is DBNull ? String.Empty : (string)dr["StandardIDs"];
                cnvp_products.StandardValues = dr["StandardValues"] is DBNull ? String.Empty : (string)dr["StandardValues"];
                cnvp_products.ImgProLoad = dr["ImgProLoad"] is DBNull ? String.Empty : (string)dr["ImgProLoad"];
                cnvp_products.HotLink = dr["HotLink"] is DBNull ? String.Empty : (string)dr["HotLink"];
                cnvp_products.DeliveryFreightID = dr["DeliveryFreightID"] is DBNull ? (int)0 : (int)dr["DeliveryFreightID"];
            } 
            return cnvp_products;
        }

        public Model.CNVP_Products LoadByIdAndDeliveryFreightID(int ID, int DeliveryFreightID)
        {

            StringBuilder sql = new StringBuilder();
            sql.Append("select [ProductName],[ProductClassID],[ProductTypeID],[BrandID],[Intro],[Detaile],[MarketPrice],[TradePrice],[ProductCode],[UpTime],[DownTime],[EditTime],[KeyWords],[ProductUnit],[ProductWeight],[CostPrice],[ProductNo],[StockTotalNum],[Allocation],[ProductTags],[IsDelete],[ProductState],[SeoInfoID],[AddTime],[StandardIDs],[StandardValues],[ImgProLoad],[HotLink],[AllowReview],[DeliveryFreightID] from " + Prefix + "Products");
            sql.Append(" where [ID] = @ID and DeliveryFreightID=@DeliveryFreightID");
            SqlParameter[] pra = {
                                 new SqlParameter("@ID",ID),
                                 new SqlParameter("@DeliveryFreightID",DeliveryFreightID)
						
                                    };
            DataTable DT = DbHelper.ExecuteTable(sql.ToString(), pra);
            Model.CNVP_Products cnvp_products = new Model.CNVP_Products();
            if (DT != null && DT.Rows.Count > 0)
            {
                DataRow dr = DT.Rows[0];
                cnvp_products.ProductName = dr["ProductName"] is DBNull ? String.Empty : (string)dr["ProductName"];
                cnvp_products.ProductClassID = dr["ProductClassID"] is DBNull ? 0 : (int)dr["ProductClassID"];
                cnvp_products.ProductTypeID = dr["ProductTypeID"] is DBNull ? 0 : (int)dr["ProductTypeID"];
                cnvp_products.BrandID = dr["BrandID"] is DBNull ? 0 : (int)dr["BrandID"];
                cnvp_products.Intro = dr["Intro"] is DBNull ? String.Empty : (string)dr["Intro"];
                cnvp_products.Detaile = dr["Detaile"] is DBNull ? String.Empty : (string)dr["Detaile"];
                cnvp_products.MarketPrice = dr["MarketPrice"] is DBNull ? (decimal)0.00 : (decimal)dr["MarketPrice"];
                cnvp_products.TradePrice = dr["TradePrice"] is DBNull ? (decimal)0.00 : (decimal)dr["TradePrice"];
                cnvp_products.ProductCode = dr["ProductCode"] is DBNull ? String.Empty : (string)dr["ProductCode"];
                cnvp_products.UpTime = dr["UpTime"] is DBNull ? DateTime.Now : (DateTime)dr["UpTime"];
                cnvp_products.DownTime = dr["DownTime"] is DBNull ? DateTime.Now : (DateTime)dr["DownTime"];
                cnvp_products.EditTime = dr["EditTime"] is DBNull ? DateTime.Now : (DateTime)dr["EditTime"];
                cnvp_products.KeyWords = dr["KeyWords"] is DBNull ? String.Empty : (string)dr["KeyWords"];
                cnvp_products.ProductUnit = dr["ProductUnit"] is DBNull ? String.Empty : (string)dr["ProductUnit"];
                cnvp_products.ProductWeight = dr["ProductWeight"] is DBNull ? (decimal)0.00 : (decimal)dr["ProductWeight"];
                cnvp_products.CostPrice = dr["CostPrice"] is DBNull ? (decimal)0.00 : (decimal)dr["CostPrice"];
                cnvp_products.ProductNo = dr["ProductNo"] is DBNull ? String.Empty : (string)dr["ProductNo"];
                cnvp_products.StockTotalNum = dr["StockTotalNum"] is DBNull ? 0 : (int)dr["StockTotalNum"];
                cnvp_products.Allocation = dr["Allocation"] is DBNull ? String.Empty : (string)dr["Allocation"];
                cnvp_products.ProductTags = dr["ProductTags"] is DBNull ? String.Empty : (string)dr["ProductTags"];
                cnvp_products.IsDelete = dr["IsDelete"] is DBNull ? (byte)0 : (byte)dr["IsDelete"];
                cnvp_products.AllowReview = dr["AllowReview"] is DBNull ? (byte)0 : (byte)dr["AllowReview"];
                cnvp_products.ProductState = dr["ProductState"] is DBNull ? (byte)0 : (byte)dr["ProductState"];
                cnvp_products.SeoInfoID = dr["SeoInfoID"] is DBNull ? (int)0 : (int)dr["SeoInfoID"];
                cnvp_products.AddTime = dr["AddTime"] is DBNull ? DateTime.Now : (DateTime)dr["AddTime"];
                cnvp_products.StandardIDs = dr["StandardIDs"] is DBNull ? String.Empty : (string)dr["StandardIDs"];
                cnvp_products.StandardValues = dr["StandardValues"] is DBNull ? String.Empty : (string)dr["StandardValues"];
                cnvp_products.ImgProLoad = dr["ImgProLoad"] is DBNull ? String.Empty : (string)dr["ImgProLoad"];
                cnvp_products.HotLink = dr["HotLink"] is DBNull ? String.Empty : (string)dr["HotLink"];
                cnvp_products.DeliveryFreightID = dr["DeliveryFreightID"] is DBNull ? (int)0 : (int)dr["DeliveryFreightID"];
            }
            return cnvp_products;
        }

        public Model.CNVP_Products LoadByIdandAllowReview(int ID)
        {

            StringBuilder sql = new StringBuilder();
            sql.Append("select [ProductName],[ProductClassID],[ProductTypeID],[BrandID],[Intro],[Detaile],[MarketPrice],[TradePrice],[ProductCode],[UpTime],[DownTime],[EditTime],[KeyWords],[ProductUnit],[ProductWeight],[CostPrice],[ProductNo],[StockTotalNum],[Allocation],[ProductTags],[IsDelete],[ProductState],[SeoInfoID],[AddTime],[StandardIDs],[StandardValues],[ImgProLoad],[HotLink],[AllowReview],[DeliveryFreightID] from " + Prefix + "Products");
            sql.Append(" where [AllowReview]=1 and [ID] = @ID");
            SqlParameter[] pra = {
                                 new SqlParameter("@ID",ID)
						
                                    };
            DataTable DT = DbHelper.ExecuteTable(sql.ToString(), pra);
            if (DT.Rows.Count > 0)
            {
                Model.CNVP_Products cnvp_products = new Model.CNVP_Products();
                DataRow dr = DT.Rows[0];
                cnvp_products.ProductName = dr["ProductName"] is DBNull ? String.Empty : (string)dr["ProductName"];
                cnvp_products.ProductClassID = dr["ProductClassID"] is DBNull ? 0 : (int)dr["ProductClassID"];
                cnvp_products.ProductTypeID = dr["ProductTypeID"] is DBNull ? 0 : (int)dr["ProductTypeID"];
                cnvp_products.BrandID = dr["BrandID"] is DBNull ? 0 : (int)dr["BrandID"];
                cnvp_products.Intro = dr["Intro"] is DBNull ? String.Empty : (string)dr["Intro"];
                cnvp_products.Detaile = dr["Detaile"] is DBNull ? String.Empty : (string)dr["Detaile"];
                cnvp_products.MarketPrice = dr["MarketPrice"] is DBNull ? (decimal)0.00 : (decimal)dr["MarketPrice"];
                cnvp_products.TradePrice = dr["TradePrice"] is DBNull ? (decimal)0.00 : (decimal)dr["TradePrice"];
                cnvp_products.ProductCode = dr["ProductCode"] is DBNull ? String.Empty : (string)dr["ProductCode"];
                cnvp_products.UpTime = dr["UpTime"] is DBNull ? DateTime.Now : (DateTime)dr["UpTime"];
                cnvp_products.DownTime = dr["DownTime"] is DBNull ? DateTime.Now : (DateTime)dr["DownTime"];
                cnvp_products.EditTime = dr["EditTime"] is DBNull ? DateTime.Now : (DateTime)dr["EditTime"];
                cnvp_products.KeyWords = dr["KeyWords"] is DBNull ? String.Empty : (string)dr["KeyWords"];
                cnvp_products.ProductUnit = dr["ProductUnit"] is DBNull ? String.Empty : (string)dr["ProductUnit"];
                cnvp_products.ProductWeight = dr["ProductWeight"] is DBNull ? (decimal)0.00 : (decimal)dr["ProductWeight"];
                cnvp_products.CostPrice = dr["CostPrice"] is DBNull ? (decimal)0.00 : (decimal)dr["CostPrice"];
                cnvp_products.ProductNo = dr["ProductNo"] is DBNull ? String.Empty : (string)dr["ProductNo"];
                cnvp_products.StockTotalNum = dr["StockTotalNum"] is DBNull ? 0 : (int)dr["StockTotalNum"];
                cnvp_products.Allocation = dr["Allocation"] is DBNull ? String.Empty : (string)dr["Allocation"];
                cnvp_products.ProductTags = dr["ProductTags"] is DBNull ? String.Empty : (string)dr["ProductTags"];
                cnvp_products.IsDelete = dr["IsDelete"] is DBNull ? (byte)0 : (byte)dr["IsDelete"];
                cnvp_products.AllowReview = dr["AllowReview"] is DBNull ? (byte)0 : (byte)dr["AllowReview"];
                cnvp_products.ProductState = dr["ProductState"] is DBNull ? (byte)0 : (byte)dr["ProductState"];
                cnvp_products.SeoInfoID = dr["SeoInfoID"] is DBNull ? (int)0 : (int)dr["SeoInfoID"];
                cnvp_products.AddTime = dr["AddTime"] is DBNull ? DateTime.Now : (DateTime)dr["AddTime"];
                cnvp_products.StandardIDs = dr["StandardIDs"] is DBNull ? String.Empty : (string)dr["StandardIDs"];
                cnvp_products.StandardValues = dr["StandardValues"] is DBNull ? String.Empty : (string)dr["StandardValues"];
                cnvp_products.ImgProLoad = dr["ImgProLoad"] is DBNull ? String.Empty : (string)dr["ImgProLoad"];
                cnvp_products.HotLink = dr["HotLink"] is DBNull ? String.Empty : (string)dr["HotLink"];
                cnvp_products.DeliveryFreightID = dr["DeliveryFreightID"] is DBNull ? (int)0 : (int)dr["DeliveryFreightID"];

                return cnvp_products;
            }
            else
            {
                return null;
            }
        }

        #endregion
        public DataTable GetSingleProductForTemplate(int productID)
        {
            try
            {
                string strSql = "select a.ID as ProductID,a.ProductName,a.Intro,a.Detaile as Detail,a.MarketPrice,a.TradePrice,a.ProductCode,a.UpTime,a.EditTime,a.ProductUnit,a.ProductWeight,a.CostPrice,a.ImgProLoad,a.AllowReview,a.StandardIDs,a.StockTotalNum,a.DeliveryFreightID,b.SmallPic,b.BigPic,b.Pic,c.ProTypeName,d.OriginTitle,d.OriginKey,d.OriginDescription,a.ProductState,e.BrankName from " + Prefix + "Products as a left join " + Prefix + "ProductsPic as b on a.ID= b.ProductID left join " + Prefix + "ProductType as c on a.ProductTypeID=c.ID left join " + Prefix + "SEOInfo as d on a.SeoInfoID=d.ID left join " + Prefix + "Branks as e on a.BrandID= e.ID where a.ID=@ID";
                SqlParameter[] parm = new SqlParameter[] { new SqlParameter("@ID", productID) };
                return DbHelper.ExecuteTable(strSql, parm);
            }
            catch (Exception e)
            {
                throw new Exception("用于分页的SQL语句无效:" + e.Message);
            }

        }
        public DataTable GetTopProductForTemplate()
        {
            try
            {
                string strSql = "select top 1 a.ID as ProductID,a.ProductName,a.Intro,a.Detaile as Detail,a.MarketPrice,a.TradePrice,a.ProductCode,a.UpTime,a.EditTime,a.ProductUnit,a.ProductWeight,a.CostPrice,a.ImgProLoad,a.ProductState,a.AllowReview,a.StandardIDs,a.StockTotalNum,a.DeliveryFreightID,b.SmallPic,b.BigPic,b.Pic,c.ProTypeName,d.OriginTitle,d.OriginKey,d.OriginDescription,e.BrankName from " + Prefix + "Products as a left join " + Prefix + "ProductsPic as b on a.ID= b.ProductID left join " + Prefix + "ProductType as c on a.ProductTypeID=c.ID left join " + Prefix + "SEOInfo as d on a.SeoInfoID=d.ID left join " + Prefix + "Branks as e on a.BrandID= e.ID  order by a.ID DESC";
                return DbHelper.ExecuteTable(strSql);
            }
            catch (Exception e)
            {
                throw new Exception("用于分页的SQL语句无效:" + e.Message);
            }

        }
        public DataTable GetPage(string PageName, int PageIndex, int PageSize, string OrderFields, out int RecordCount, out int PageCount, params SQLConditionInfo[] SqlCondition)
        {
            try
            {
                string sql_Temp = "";
                if (SqlCondition[0].value != null && SqlCondition[0].value.ToString() != "")
                {
                    sql_Temp += " and IsDelete = " + SqlCondition[0].value.ToString();
                }
                if (SqlCondition[10].value != null && SqlCondition[10].value.ToString() != "")
                {
                    sql_Temp += " and ProductState='" + SqlCondition[10].value.ToString() + "'";
                }
                if (SqlCondition[5].value.ToString() == "0")
                {
                    if (SqlCondition[1].value != null && SqlCondition[1].value.ToString() != "")
                    {
                        sql_Temp += " and (ProductName like '%" + SqlCondition[1].value.ToString() + "%' or ProductCode like  '%" + SqlCondition[1].value.ToString() + "%' or id in ( select ProductID from " + Prefix + "ProductsDetail where ProDetailNo like '%" + SqlCondition[1].value.ToString() + "%'))";
                    }
                }
                else  if (SqlCondition[5].value.ToString() == "1")
                {
                    if (SqlCondition[4].value != null && SqlCondition[4].value.ToString() != "")
                    {
                        sql_Temp += " and (ProductName like '%" + SqlCondition[4].value.ToString() + "%' or ProductCode like  '%" + SqlCondition[4].value.ToString() + "%' or id in ( select ProductID from " + Prefix + "ProductsDetail where ProDetailNo like '%" + SqlCondition[4].value.ToString() + "%'))";
                    }
                    if (SqlCondition[2].value != null && SqlCondition[2].value.ToString() != "0")
                    {
                        sql_Temp += " and ProductClassID='" + SqlCondition[2].value.ToString() + "'";
                    }
                    if (SqlCondition[3].value != null && SqlCondition[3].value.ToString() != "0")
                    {
                        sql_Temp += " and ProductTypeID='" + SqlCondition[3].value.ToString() + "'";
                    }
                    if (SqlCondition[6].value != null && SqlCondition[6].value.ToString() != "0")
                    {
                        sql_Temp += " and BrandID='" + SqlCondition[6].value.ToString() + "'";
                    }
                    if (SqlCondition[10].value != null && SqlCondition[10].value.ToString() != "")
                    {
                        sql_Temp += " and ProductState='" + SqlCondition[10].value.ToString() + "'";
                    }
                }
                else if (SqlCondition[5].value.ToString() == "2")
                {
                    if (SqlCondition[7].value != null && SqlCondition[7].value.ToString() != "0")
                    {
                        sql_Temp += " and ProductClassID='" + SqlCondition[7].value.ToString() + "'";
                    }

                }
                else if (SqlCondition[5].value.ToString() == "3")
                { 
                    if (SqlCondition[8].value != null && SqlCondition[8].value.ToString() != "0")
                    {
                        sql_Temp += " and ProductTypeID='" + SqlCondition[8].value.ToString() + "'";
                    }
                }
                else if (SqlCondition[5].value.ToString() == "4")
                {
                    if (SqlCondition[9].value != null && SqlCondition[9].value.ToString() != "0")
                    {
                        sql_Temp += " and BrandID='" + SqlCondition[9].value.ToString() + "'";
                    }
                } 
                string AllFields = "ID, ProductName, ProductClassID, ProductTypeID, BrandID, Intro, Detaile, MarketPrice, TradePrice, ProductCode, UpTime, DownTime, EditTime, KeyWords, ";
                AllFields += "  ProductUnit, ProductWeight, CostPrice, ProductNo, StockTotalNum, Allocation, ProductTags, IsDelete, ProductState, SeoInfoID, AddTime,StandardIDs, StandardValues,ImgProLoad";
                string Condition = " " + Prefix + "Products where 1=1" + sql_Temp + "";
                string IndexField = "ID";
                //string OrderFields = "order by Id Desc";
                if (OrderFields.Length == 0)
                {
                    OrderFields = "order by Id Desc";
                }
                else
                {
                    OrderFields = " ORDER BY " + OrderFields;
                }
                return DbHelper.ExecutePage(AllFields, Condition, IndexField, OrderFields, PageIndex, PageSize, out RecordCount, out PageCount, null);
            }
            catch (Exception e)
            {
                throw new Exception("用于分页的SQL语句无效:" + e.Message);
            }

        }


        public DataTable GetPage(string PageName, int PageIndex, int PageSize, out int RecordCount, out int PageCount, params SQLConditionInfo[] SqlCondition)
        {
            try
            {
                string sql_Temp = "";
                if (SqlCondition[0].value != null && SqlCondition[0].value.ToString() != "")
                {
                    sql_Temp += " and IsDelete = " + SqlCondition[0].value.ToString();
                }
                if (SqlCondition[10].value != null && SqlCondition[10].value.ToString() != "")
                {
                    sql_Temp += " and ProductState='" + SqlCondition[10].value.ToString() + "'";
                }
                if (SqlCondition[5].value.ToString() == "0")
                {
                    if (SqlCondition[1].value != null && SqlCondition[1].value.ToString() != "")
                    {
                        sql_Temp += " and (ProductName like '%" + SqlCondition[1].value.ToString() + "%' or ProductCode like  '%" + SqlCondition[1].value.ToString() + "%' or id in ( select ProductID from " + Prefix + "ProductsDetail where ProDetailNo like '%" + SqlCondition[1].value.ToString() + "%'))";
                    }
                }
                else if (SqlCondition[5].value.ToString() == "1")
                {
                    if (SqlCondition[4].value != null && SqlCondition[4].value.ToString() != "")
                    {
                        sql_Temp += " and (ProductName like '%" + SqlCondition[4].value.ToString() + "%' or ProductCode like  '%" + SqlCondition[4].value.ToString() + "%' or id in ( select ProductID from " + Prefix + "ProductsDetail where ProDetailNo like '%" + SqlCondition[4].value.ToString() + "%'))";
                    }
                    if (SqlCondition[2].value != null && SqlCondition[2].value.ToString() != "0")
                    {
                        sql_Temp += " and ProductClassID='" + SqlCondition[2].value.ToString() + "'";
                    }
                    if (SqlCondition[3].value != null && SqlCondition[3].value.ToString() != "0")
                    {
                        sql_Temp += " and ProductTypeID='" + SqlCondition[3].value.ToString() + "'";
                    }
                    if (SqlCondition[6].value != null && SqlCondition[6].value.ToString() != "0")
                    {
                        sql_Temp += " and BrandID='" + SqlCondition[6].value.ToString() + "'";
                    }
                    if (SqlCondition[10].value != null && SqlCondition[10].value.ToString() != "")
                    {
                        sql_Temp += " and ProductState='" + SqlCondition[10].value.ToString() + "'";
                    }
                }
                else if (SqlCondition[5].value.ToString() == "2")
                {
                    if (SqlCondition[7].value != null && SqlCondition[7].value.ToString() != "0")
                    {
                        sql_Temp += " and ProductClassID='" + SqlCondition[7].value.ToString() + "'";
                    }

                }
                else if (SqlCondition[5].value.ToString() == "3")
                {
                    if (SqlCondition[8].value != null && SqlCondition[8].value.ToString() != "0")
                    {
                        sql_Temp += " and ProductTypeID='" + SqlCondition[8].value.ToString() + "'";
                    }
                }
                else if (SqlCondition[5].value.ToString() == "4")
                {
                    if (SqlCondition[9].value != null && SqlCondition[9].value.ToString() != "0")
                    {
                        sql_Temp += " and BrandID='" + SqlCondition[9].value.ToString() + "'";
                    }
                }
                string AllFields = "ID, ProductName, ProductClassID, ProductTypeID, BrandID, Intro, Detaile, MarketPrice, TradePrice, ProductCode, UpTime, DownTime, EditTime, KeyWords, ";
                AllFields += "  ProductUnit, ProductWeight, CostPrice, ProductNo, StockTotalNum, Allocation, ProductTags, IsDelete, ProductState, SeoInfoID, AddTime,StandardIDs, StandardValues,ImgProLoad";
                string Condition = " " + Prefix + "Products where 1=1" + sql_Temp + "";
                string IndexField = "ID";
                string OrderFields = "order by Id Desc";
                return DbHelper.ExecutePage(AllFields, Condition, IndexField, OrderFields, PageIndex, PageSize, out RecordCount, out PageCount, null);
            }
            catch (Exception e)
            {
                throw new Exception("用于分页的SQL语句无效:" + e.Message);
            }
        }

        public DataTable GetPageKey(string PageName, int PageIndex, int PageSize, out int RecordCount, out int PageCount, params SQLConditionInfo[] SqlCondition)
        {
            try
            {
                string sql_Temp = "";
                if (SqlCondition[0].value != null && SqlCondition[0].value.ToString() != "")
                {
                    sql_Temp += " and a.ProductNo like '%" + SqlCondition[0].value.ToString() + "%' OR a.ProductName like '%" + SqlCondition[0].value.ToString() + "%'";
                }
                sql_Temp += " and a.StockTotalNum<>0";
                string AllFields = "a.ID, a.ProductName, a.ProductClassID, a.ProductNo, a.StockTotalNum, a.TradePrice,b.ID as ProDetailID, b.ProDetailNo,b.StockNum,b.SalePrice, b.StandardValueIDs ";
                string Condition = " " + Prefix + "Products as a FULL JOIN " + Prefix + "ProductsDetail as b on a.ID = b.ProductID where 1=1 " + sql_Temp + " ";
                string IndexField = "a.ID";
                string OrderFields = " order by a.ID Desc";
                return DbHelper.ExecutePage(AllFields, Condition, IndexField, OrderFields, PageIndex, PageSize, out RecordCount, out PageCount, null);
            }
            catch (Exception e)
            {
                throw new Exception("用于分页的SQL语句无效:" + e.Message);
            }
        }

        public List<Model.CNVP_Products> QurryListFK_CNVP_Products_REFERENCE_CNVP_Branks(int BrandID)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select [ID],[ProductName],[ProductClassID],[ProductTypeID],[Intro],[Detaile],[MarketPrice],[TradePrice],[ProductCode],[UpTime],[DownTime],[EditTime],[KeyWords],[ProductUnit],[ProductWeight],[CostPrice],[ProductNo],[StockTotalNum],[Allocation],[ProductTags],[IsDelete],[ProductState],[AddTime],[StandardIDs],[StandardValues],[ImgProLoad],[AllowReview] from " + Prefix + "Products");
            sql.Append(" where BrandID=@BrandID");
            SqlParameter[] pra = {
                                new SqlParameter("@BrandID",BrandID)

                                };
            DataTable DT = DbHelper.ExecuteTable(sql.ToString(),pra);
            List<Model.CNVP_Products> list = new List<Model.CNVP_Products>();
            if (DT != null && DT.Rows.Count > 0)
            {
                foreach (DataRow dr in DT.Rows)
                {
                    Model.CNVP_Products cnvp_products = new Model.CNVP_Products();
                    cnvp_products.ID = dr["ID"] is DBNull ? 0 : (int)dr["ID"];
                    cnvp_products.ProductName = dr["ProductName"] is DBNull ? String.Empty : (string)dr["ProductName"];
                    cnvp_products.ProductClassID = dr["ProductClassID"] is DBNull ? 0 : (int)dr["ProductClassID"];
                    cnvp_products.ProductTypeID = dr["ProductTypeID"] is DBNull ? 0 : (int)dr["ProductTypeID"];
                    cnvp_products.Intro = dr["Intro"] is DBNull ? String.Empty : (string)dr["Intro"];
                    cnvp_products.Detaile = dr["Detaile"] is DBNull ? String.Empty : (string)dr["Detaile"];
                    cnvp_products.MarketPrice = dr["MarketPrice"] is DBNull ? (decimal)0.00 : (decimal)dr["MarketPrice"];
                    cnvp_products.TradePrice = dr["TradePrice"] is DBNull ? (decimal)0.00 : (decimal)dr["TradePrice"];
                    cnvp_products.ProductCode = dr["ProductCode"] is DBNull ? String.Empty : (string)dr["ProductCode"];
                    cnvp_products.UpTime = dr["UpTime"] is DBNull ? DateTime.Now : (DateTime)dr["UpTime"];
                    cnvp_products.DownTime = dr["DownTime"] is DBNull ? DateTime.Now : (DateTime)dr["DownTime"];
                    cnvp_products.EditTime = dr["EditTime"] is DBNull ? DateTime.Now : (DateTime)dr["EditTime"];
                    cnvp_products.KeyWords = dr["KeyWords"] is DBNull ? String.Empty : (string)dr["KeyWords"];
                    cnvp_products.ProductUnit = dr["ProductUnit"] is DBNull ? String.Empty : (string)dr["ProductUnit"];
                    cnvp_products.ProductWeight = dr["ProductWeight"] is DBNull ? (decimal)0.00 : (decimal)dr["ProductWeight"];
                    cnvp_products.CostPrice = dr["CostPrice"] is DBNull ? (decimal)0.00 : (decimal)dr["CostPrice"];
                    cnvp_products.ProductNo = dr["ProductNo"] is DBNull ? String.Empty : (string)dr["ProductNo"];
                    cnvp_products.StockTotalNum = dr["StockTotalNum"] is DBNull ? 0 : (int)dr["StockTotalNum"];
                    cnvp_products.Allocation = dr["Allocation"] is DBNull ? String.Empty : (string)dr["Allocation"];
                    cnvp_products.ProductTags = dr["ProductTags"] is DBNull ? String.Empty : (string)dr["ProductTags"];
                    cnvp_products.IsDelete = dr["IsDelete"] is DBNull ? (byte)0 : (byte)dr["IsDelete"];
                    cnvp_products.AllowReview = dr["AllowReview"] is DBNull ? (byte)0 : (byte)dr["AllowReview"];
                    cnvp_products.ProductState = dr["ProductState"] is DBNull ? (byte)0 : (byte)dr["ProductState"];
                    cnvp_products.AddTime = dr["AddTime"] is DBNull ? DateTime.Now : (DateTime)dr["AddTime"];
                    cnvp_products.StandardIDs = dr["StandardIDs"] is DBNull ? String.Empty : (string)dr["StandardIDs"];
                    cnvp_products.StandardValues = dr["StandardValues"] is DBNull ? String.Empty : (string)dr["StandardValues"];
                    cnvp_products.ImgProLoad = dr["ImgProLoad"] is DBNull ? String.Empty : (string)dr["ImgProLoad"];
                    list.Add(cnvp_products);
                }
            }
            return list;
        }

        public int UpdateByIsDelete(Model.CNVP_Products obj)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("Update " + Prefix + "Products set IsDelete = @IsDelete where [ID] = @ID");
            SqlParameter[] pra = {
                                new SqlParameter("@ID",obj.ID),
                                new SqlParameter("@IsDelete",obj.IsDelete)
                                };
            return DbHelper.ExecuteNonQuery(sql.ToString(), pra);
        }
        public int GetTopProductDetailID(int columnID)
        {
            string StrSql = "Select top 1 AddressRoute  From " + Prefix + "Column Where ID=@ColumnID";
            SqlParameter[] param = { DbHelper.MakeInParam("@ColumnID", SqlDbType.Int, 4, columnID) };
            DataTable dt = DbHelper.ExecuteTable(StrSql, param);
            string AddressRoute = String.Empty;
            if (dt == null || dt.Rows.Count == 0)
            {
                if (columnID == 0)
                {
                    AddressRoute = "0";
                }
                else
                {
                    AddressRoute = "0," + columnID.ToString();
                }
            }
            else
            {
                AddressRoute = dt.Rows[0]["AddressRoute"] + "," +columnID.ToString();
            }
            dt.Clear();
            StrSql = "select top 1 id from " + Prefix + "Products where ProductClassID in (" + AddressRoute + ") order by charindex(tClassIDCast(Produc as VarChar(50)),'," + AddressRoute + ",') desc ,id desc";
            dt = DbHelper.ExecuteTable(StrSql);
            if (dt == null || dt.Rows.Count == 0)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(dt.Rows[0]["id"]);
            }
        }
        public int UpdateByProductState(Model.CNVP_Products obj)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("Update " + Prefix + "Products set ProductState = @ProductState where [ID] = @ID");
            SqlParameter[] pra = {
                                new SqlParameter("@ID",obj.ID),
                                new SqlParameter("@ProductState",obj.ProductState)
                                };
            return DbHelper.ExecuteNonQuery(sql.ToString(), pra);
        }

        /// <summary>
        /// 配送更新库存 -库存
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public int UpdateNum(int ProductsID, int OrderNum, int type)
        {
            StringBuilder sql = new StringBuilder();
            if (type == 0)
            {
                sql.Append("update " + Prefix + "Products set [StockTotalNum] = StockTotalNum - @OrderNum where [ID] = @ID");
            }
            else
            {
                sql.Append("update " + Prefix + "Products set [StockTotalNum] = StockTotalNum + @OrderNum where [ID] = @ID");
            }
            SqlParameter[] pra = { 
                            new SqlParameter("@ID",ProductsID),
							new SqlParameter("@OrderNum",OrderNum)
                                };
            return DbHelper.ExecuteNonQuery(sql.ToString(), pra);
        }
    }
}

